import { Service } from 'egg';
import { Activity } from '../model/Activity';
import { School } from '../model/School';
import { ActivityEnroll } from '../model/Activity_enroll';
import { IdentifyingCode } from '../model/Identifying_code';

class ActivityService extends Service {
    /**
     * 根据条件搜索活动列表
     * @param param 
     */
    public async searchActivityList(param: any) {
        const offset = (Number(param.page) - 1) * Number(param.size);
        const limit = Number(param.size);
        let queryStr = `
                SELECT
                * 
            FROM
                (
                (
            SELECT
                activity_id,
                activity_nature,
                activity_type,
                activity_title,
                activity_sponsors_phone,
                activity_status,
                activity_auditing_status,
                created_at
            FROM
                t_activity 
            WHERE
                is_delete = '1' 
                AND activity_type = '1' 
                AND (activity_school_id IS NULL 
                OR activity_school_id ='')
                ) UNION 
                (
            SELECT
                a.activity_id,
                a.activity_nature,
                a.activity_type,
                a.activity_title,
                a.activity_sponsors_phone,
                a.activity_status,
                a.activity_auditing_status,
                a.created_at
            FROM
                t_activity a
                INNER JOIN t_school s ON s.school_id = a.activity_school_id 
                AND s.school_status = '1' 
            WHERE
                a.is_delete = '1' 
                AND a.activity_type = '1' 
                ) UNION 
                (
            SELECT
                a.activity_id,
                a.activity_nature,
                a.activity_type,
                a.activity_title,
                a.activity_sponsors_phone,
                a.activity_status,
                a.activity_auditing_status,
                a.created_at
            FROM
                t_activity a
                INNER JOIN t_school s ON s.school_id = a.activity_school_id 
                AND s.school_status = '1'
                INNER JOIN (
            SELECT
                u.user_id 
            FROM
                t_user u
                INNER JOIN t_college c ON u.user_college_id = c.college_id 
                AND c.college_status = '1' 
                AND u.is_delete = '1' 
                AND u.user_status <> '1' 
                ) temp ON a.user_id = temp.user_id 
            WHERE
                a.is_delete = '1' 
                AND a.activity_type = '2' 
                ) 
                ) AS a 
                    WHERE`;
        if (param.activity_nature) {
            queryStr += ` a.activity_nature= :activity_nature AND`;
        }
        if (param.activity_type) {
            queryStr += ` a.activity_type= :activity_type AND`;
        }
        if (param.activity_title) {
            queryStr += ` a.activity_title LIKE :activity_title AND`;
        }
        if (param.activity_sponsors_phone) {
            queryStr += ` a.activity_sponsors_phone LIKE :activity_sponsors_phone AND`;
        }
        if (param.activity_status) {
            queryStr += ` a.activity_status= :activity_status AND`;
        }
        if (param.activity_auditing_status) {
            queryStr += ` a.activity_auditing_status= :activity_auditing_status AND`;
        }
        const queryCount = queryStr + ` '1'='1' ORDER BY a.created_at DESC`;
        queryStr += ` '1'='1' ORDER BY a.created_at DESC limit ${offset},${limit}`;
        const activity: any = await this.ctx.model.query(queryStr, {
            type: this.ctx.model.QueryTypes.SELECT,
            replacements: {
                activity_nature: param.activity_nature,
                activity_type: param.activity_type,
                activity_title: '%' + param.activity_title + '%',
                activity_sponsors_phone: '%' + param.activity_sponsors_phone + '%',
                activity_status: param.activity_status,
                activity_auditing_status: param.activity_auditing_status
            }
        })
        const count = await this.ctx.model.query(queryCount, {
            type: this.ctx.model.QueryTypes.SELECT,
            replacements: {
                activity_nature: param.activity_nature,
                activity_type: param.activity_type,
                activity_title: '%' + param.activity_title + '%',
                activity_sponsors_phone: '%' + param.activity_sponsors_phone + '%',
                activity_status: param.activity_status,
                activity_auditing_status: param.activity_auditing_status
            }
        });
        if (activity.length <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
        }
        let returnDataArray: any = [];
        for (let i = 0; i < activity.length; i++) {
            const queryStr = `
            SELECT
                a.activity_id,
                a.activity_title,
                a.user_id,
                a.activity_sponsors,
                a.activity_sponsors_phone,
                a.activity_nature,
                a.activity_type,
                a.activity_start_time,
                a.activity_end_time,
                a.activity_place,
                a.activity_detail,
                a.activity_detail_picture,
                a.activity_cover,
                a.activity_status,
                a.activity_auditing_status,
                s.school_name,
                c.college_name            
            FROM
                t_activity a
                LEFT JOIN t_school s ON a.activity_school_id = s.school_id
                LEFT JOIN t_college c ON a.activity_college_id = c.college_id
            WHERE
                a.activity_id = :activity_id 
                AND a.is_delete = '1'
            `;
            const oneActivity: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { activity_id: activity[i].activity_id }
            })
            let returnData: any = {};
            returnData.activity_id = oneActivity[0].activity_id;
            returnData.activity_title = oneActivity[0].activity_title;
            returnData.user_id = oneActivity[0].user_id;
            returnData.activity_sponsors = oneActivity[0].activity_sponsors;
            returnData.activity_sponsors_phone = oneActivity[0].activity_sponsors_phone;
            returnData.activity_nature = oneActivity[0].activity_nature;
            returnData.activity_type = oneActivity[0].activity_type;
            returnData.activity_start_time = oneActivity[0].activity_start_time;
            returnData.activity_end_time = oneActivity[0].activity_end_time;
            returnData.activity_place = oneActivity[0].activity_place;
            returnData.activity_detail = oneActivity[0].activity_detail;
            returnData.activity_detail_picture = oneActivity[0].activity_detail_picture;
            returnData.activity_cover = oneActivity[0].activity_cover;
            returnData.activity_status = oneActivity[0].activity_status;
            returnData.activity_auditing_status = oneActivity[0].activity_auditing_status;
            returnData.school_name = oneActivity[0].school_name;
            returnData.college_name = oneActivity[0].college_name;
            let query = `select u.* from t_activity_enroll a inner join t_user u on a.user_id=u.user_id and a.activity_id=:activity_id and u.user_status <> '1' and u.user_auditing_status<>3 
            inner join t_college c on u.user_college_id=c.college_id and c.college_status='1' inner join t_school s on u.user_school_id=s.school_id and s.school_status='1' where a.activity_enroll_status='1'`
            const activityEnroll = await this.ctx.model.query(query, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { activity_id: activity[i].activity_id }
            });
            returnData.activity_people = activityEnroll.length;
            returnDataArray.push(returnData);
        }
        const result = { count: count.length, activityList: returnDataArray };
        return result;
    }

    /**
     * 根据条件搜索活动列表-微信
     * @param param 
     */
    public async searchWxActivityList(param: any) {
        await this.checkActivityStatus();
        const offset = (Number(param.page) - 1) * Number(param.size);
        const limit = Number(param.size);
        let user_id = param.user_id;
        if (!user_id) {
            user_id = '';
        }
        let queryStr: string = ``;
        if (!user_id) {
            queryStr = `
                SELECT
                    * 
                FROM
                    (
                    (
                SELECT
                    activity_id,
                    activity_title,
                    activity_place,
                    activity_detail,
                    activity_status,
                    created_at 
                FROM
                    t_activity 
                WHERE
                    is_delete = '1' 
                    AND activity_type = '1' 
                    AND activity_nature =:activity_nature 
                    AND activity_auditing_status='2' 
                    AND (activity_school_id IS NULL 
                    OR activity_school_id= '') 
                    ) UNION 
                    (
                SELECT
                    a.activity_id,
                    a.activity_title,
                    a.activity_place,
                    a.activity_detail,
                    a.activity_status,
                    a.created_at 
                FROM
                    t_activity a
                    INNER JOIN t_school s ON s.school_id = a.activity_school_id 
                    AND s.school_status = '1' 
                WHERE
                    a.is_delete = '1' 
                    AND a.activity_type = '1' 
                    AND a.activity_nature =:activity_nature 
                    AND a.activity_auditing_status='2'
                    ) UNION
                    (
                SELECT
                    a.activity_id,
                    a.activity_title,
                    a.activity_place,
                    a.activity_detail,
                    a.activity_status,
                    a.created_at 
                FROM
                    t_activity a
                    INNER JOIN t_school s ON s.school_id = a.activity_school_id 
                    AND s.school_status = '1'
                    INNER JOIN (
                SELECT
                    u.user_id 
                FROM
                    t_user u
                    INNER JOIN t_college c ON u.user_college_id = c.college_id 
                    AND c.college_status = '1' 
                    AND u.is_delete = '1' 
                    AND u.user_status <> '1' 
                    ) temp ON a.user_id = temp.user_id 
                WHERE
                    a.is_delete = '1' 
                    AND a.activity_type = '2' 
                    AND a.activity_nature =:activity_nature 
                    AND a.activity_auditing_status='2'
                    ) 
                    ) AS a`;
        } else {
            queryStr = `
                SELECT
                    * 
                FROM
                    (
                    (
                SELECT
                    activity_id,
                    activity_title,
                    activity_place,
                    activity_detail,
                    activity_status,
                    created_at 
                FROM
                    t_activity 
                WHERE
                    is_delete = '1' 
                    AND activity_type = '1'  
                    AND activity_nature = :activity_nature 
                    AND activity_auditing_status='2' 
                    AND (activity_school_id IS NULL 
                    OR activity_school_id ='') 
                    ) UNION 
                    (
                SELECT
                    a.activity_id,
                    a.activity_title,
                    a.activity_place,
                    a.activity_detail,
                    a.activity_status,
                    a.created_at 
                FROM
                    t_activity a
                    INNER JOIN t_school s ON s.school_id = a.activity_school_id 
                    AND s.school_status = '1' 
                WHERE
                    a.is_delete = '1' 
                    AND a.activity_school_id = :school_id 
                    AND a.activity_type = '1' 
                    AND a.activity_nature = :activity_nature 
                    AND a.activity_auditing_status='2'
                    ) UNION 
                    (
                SELECT
                    a.activity_id,
                    a.activity_title,
                    a.activity_place,
                    a.activity_detail,
                    a.activity_status,
                    a.created_at 
                FROM
                    t_activity a
                    INNER JOIN t_school s ON s.school_id = a.activity_school_id 
                    AND s.school_status = '1'
                    INNER JOIN (
                SELECT
                    u.user_id 
                FROM
                    t_user u
                    INNER JOIN t_college c ON u.user_college_id = c.college_id 
                    AND c.college_status = '1' 
                    AND u.is_delete = '1' 
                    AND u.user_status <> '1' 
                    ) temp ON a.user_id = temp.user_id 
                WHERE
                    a.is_delete = '1' 
                    AND a.activity_type = '2' 
                    AND a.activity_school_id = :school_id 
                    AND a.activity_nature = :activity_nature 
                    AND a.activity_auditing_status='2'
                    ) 
                    ) AS a`;
        }
        const submitData: any = {};
        submitData.activity_nature = param.activity_nature;
        if (param.parameter) {
            queryStr += ` WHERE (a.activity_title LIKE :parameter OR a.activity_detail LIKE :parameter)`;
            submitData.parameter = '%' + param.parameter + '%';
        }
        if (user_id) {
            submitData.school_id = param.school_id;
        }
        const queryCount = queryStr + ` ORDER BY cast( a.activity_status as SIGNED  INTEGER),a.created_at DESC`;
        queryStr += ` ORDER BY cast( a.activity_status as SIGNED  INTEGER),a.created_at DESC LIMIT ${offset},${limit}`;
        const activity: any = await this.ctx.model.query(queryStr, {
            type: this.ctx.model.QueryTypes.SELECT,
            replacements: submitData
        })
        const count = await this.ctx.model.query(queryCount, {
            type: this.ctx.model.QueryTypes.SELECT,
            replacements: submitData
        });
        if (activity.length <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
        }
        let returnDataArray: any = [];
        for (let i = 0; i < activity.length; i++) {
            const queryStr = `
            SELECT
                a.activity_id,
                a.activity_title,
                a.user_id,
                a.activity_sponsors,
                a.activity_sponsors_phone,
                a.activity_nature,
                a.activity_type,
                a.activity_start_time,
                a.activity_end_time,
                a.activity_place,
                a.activity_suto,
                a.activity_detail,
                a.activity_detail_picture,
                a.activity_cover,
                a.activity_status,
                s.school_name,
                c.college_name            
            FROM
                t_activity a
                LEFT JOIN t_school s ON a.activity_school_id = s.school_id
                LEFT JOIN t_college c ON a.activity_college_id = c.college_id
            WHERE
                a.activity_id = :activity_id 
                AND a.is_delete = '1'
            `;
            const oneActivity: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { activity_id: activity[i].activity_id }
            })
            let returnData: any = {};
            returnData.activity_id = oneActivity[0].activity_id;
            returnData.activity_title = oneActivity[0].activity_title;
            returnData.user_id = oneActivity[0].user_id;
            returnData.activity_sponsors = oneActivity[0].activity_sponsors;
            returnData.activity_sponsors_phone = oneActivity[0].activity_sponsors_phone;
            returnData.activity_nature = oneActivity[0].activity_nature;
            returnData.activity_type = oneActivity[0].activity_type;
            returnData.activity_start_time = oneActivity[0].activity_start_time;
            returnData.activity_end_time = oneActivity[0].activity_end_time;
            returnData.activity_place = oneActivity[0].activity_place;
            returnData.activity_suto = oneActivity[0].activity_suto;
            returnData.activity_detail = oneActivity[0].activity_detail;
            returnData.activity_detail_picture = oneActivity[0].activity_detail_picture;
            returnData.activity_cover = oneActivity[0].activity_cover;
            returnData.activity_status = oneActivity[0].activity_status;
            returnData.school_name = oneActivity[0].school_name;
            returnData.college_name = oneActivity[0].college_name;
            if (!user_id) {
                returnData.is_enroll = '0';
                returnData.is_self = '2';
            } else {
                const submitData = { activity_id: returnData.activity_id, user_id: param.user_id }
                const status = await this.checkIsEnroll(submitData);
                returnData.is_enroll = status;
                if (returnData.user_id == user_id) {
                    returnData.is_self = '1';
                } else {
                    returnData.is_self = '2';
                }
            }
            returnDataArray.push(returnData);
        }
        const result = { count: count.length, activityList: returnDataArray };
        return result;
    }

    /**
     * 判断当前用户参与活动的状态
     * @param param 
     */
    public async checkIsEnroll(param: any) {
        const result: any = await this.ctx.model.ActivityEnroll.findOne<ActivityEnroll>({
            attributes: ['activity_enroll_status'],
            where: { activity_id: param.activity_id, user_id: param.user_id, is_delete: '1' }
        });
        if (!result) {
            return '0';
        }
        return result.activity_enroll_status
    }

    /**
     * 根据活动id查找活动详情
     * @param param 
     */
    public async searchActivityById(param: any) {
        const queryStr = `
            SELECT
                a.activity_id,
                a.activity_title,
                a.user_id,
                a.activity_sponsors,
                a.activity_sponsors_phone,
                a.activity_nature,
                a.activity_type,
                a.activity_start_time,
                a.activity_end_time,
                a.activity_place,
                a.activity_suto,
                a.activity_detail,
                a.activity_detail_picture,
                a.activity_cover,
                a.activity_status,
                a.activity_auditing_status,
                s.school_name,
                c.college_name            
            FROM
                t_activity a
                LEFT JOIN t_school s ON a.activity_school_id = s.school_id
                LEFT JOIN t_college c ON a.activity_college_id = c.college_id
            WHERE
                a.activity_id = :activity_id 
                AND a.is_delete = '1'
            `;
        const oneActivity: any = await this.ctx.model.query(queryStr, {
            type: this.ctx.model.QueryTypes.SELECT,
            replacements: { activity_id: param.activity_id }
        })
        if (oneActivity.length <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '没有该活动' }));
        }
        let returnData: any = {};
        returnData.activity_id = oneActivity[0].activity_id;
        returnData.activity_title = oneActivity[0].activity_title;
        returnData.user_id = oneActivity[0].user_id;
        returnData.activity_sponsors = oneActivity[0].activity_sponsors;
        returnData.activity_sponsors_phone = oneActivity[0].activity_sponsors_phone;
        returnData.activity_nature = oneActivity[0].activity_nature;
        returnData.activity_type = oneActivity[0].activity_type;
        returnData.activity_start_time = oneActivity[0].activity_start_time;
        returnData.activity_end_time = oneActivity[0].activity_end_time;
        returnData.activity_place = oneActivity[0].activity_place;
        returnData.activity_suto = oneActivity[0].activity_suto;
        returnData.activity_detail = oneActivity[0].activity_detail;
        returnData.activity_detail_picture = oneActivity[0].activity_detail_picture;
        returnData.activity_cover = oneActivity[0].activity_cover;
        returnData.activity_status = oneActivity[0].activity_status;
        returnData.activity_auditing_status = oneActivity[0].activity_auditing_status;
        returnData.school_name = oneActivity[0].school_name;
        returnData.college_name = oneActivity[0].college_name;
        if (returnData.user_id && returnData.user_id.length > 0) {
            const queryCreated = `
                    SELECT
                        u.user_id,
                        u.user_nickname,
                        u.user_avatarUrl,
                        u.user_sex,
                        u.user_sound_url,
                        u.user_sound_status,
                        u.user_phone,
                        s.school_name,
                        s.school_badge,
                        c.college_name,
                        u.user_signature 
                    FROM
                        t_user u
                        INNER JOIN t_school s ON u.user_school_id = s.school_id 
                        AND s.school_status = '1' AND u.user_id=:user_id
                        INNER JOIN t_college c ON u.user_college_id = c.college_id 
                        AND c.college_status = '1' 
                    WHERE
                        u.user_status <> '1' 
                        AND u.user_auditing_status <>3`;
            const result: any = await this.ctx.model.query(queryCreated, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { user_id: returnData.user_id }
            })
            returnData.creater = result[0];
            // userCreated.activity_enroll_id='';
            // userCreated.user_id=result[0].user_id;
            // userCreated.user_nickname=result[0].user_nickname;
            // userCreated.user_avatarUrl=result[0].user_avatarUrl;
            // userCreated.user_sex=result[0].user_sex;
            // userCreated.user_phone=result[0].user_phone;
            // userCreated.school_name=result[0].school_name;
            // userCreated.school_badge=result[0].school_badge;
            // userCreated.college_name=result[0].college_name;
            // userCreated.user_signature=result[0].user_signature;
        }
        let query = `
            SELECT
                a.activity_enroll_id,
                a.phone,
                a.weChat_account,
                u.user_id,
                u.user_nickname,
                u.user_avatarUrl,
                u.user_sex,
                u.user_sound_url,
                u.user_sound_status,
                u.user_phone,
                s.school_name,
                s.school_badge,
                c.college_name,
                u.user_signature 
            FROM
                t_activity_enroll a
                INNER JOIN t_user u ON a.user_id = u.user_id 
                AND u.is_delete = '1'
                INNER JOIN t_school s ON u.user_school_id = s.school_id 
                AND s.school_status = '1'
                INNER JOIN t_college c ON u.user_college_id = c.college_id 
                AND c.college_status = '1' 
            WHERE
                a.activity_enroll_status = '1' 
                AND a.is_delete = '1' 
                AND a.activity_id = :activity_id`;
        const activityEnroll = await this.ctx.model.query(query, {
            type: this.ctx.model.QueryTypes.SELECT,
            replacements: { activity_id: param.activity_id }
        });
        let activity_people = activityEnroll.length;
        if (!param.user_id || param.user_id <= 0) {
            returnData.is_enroll = '0';
            returnData.is_self = '2';
        } else {
            const submitData = { activity_id: param.activity_id, user_id: param.user_id }
            const status = await this.checkIsEnroll(submitData);
            returnData.is_enroll = status;
            if (returnData.user_id == param.user_id) {
                returnData.is_self = '1';
            } else {
                returnData.is_self = '2';
            }
        }
        returnData.activity_people = activity_people;
        returnData.userList = activityEnroll;
        return returnData;
    }

    /**
     * 启动、暂停活动
     * @param param 
     */
    public async startAndStopActivity(param: any) {
        const activity = await this.ctx.model.Activity.update(
            { activity_status: param.type },
            { where: { activity_id: param.activity_id } },
        );
        if (activity[0] <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '操作失败' }));
        }
    }

    /**
     * 启动、暂停活动-微信
     * @param param 
     */
    public async startAndStopWxActivity(param: any) {
        const activity = await this.ctx.model.Activity.update(
            { activity_status: param.type },
            { where: { activity_id: param.activity_id, user_id: param.user_id } },
        );
        if (activity[0] <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '操作失败' }));
        }
    }

    /**
     * 结束活动-微信
     * @param param 
     */
    public async endingActivity(param: any) {
        const result: any = await this.ctx.model.Activity.update(
            { activity_status: 3 },
            { where: { activity_id: param.activity_id, user_id: param.user_id } }
        );
        if (result[0] <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '操作失败' }));
        }
    }

    /**
     * 结束活动-后台
     * @param param 
     */
    public async endingAdminActivity(param: any) {
        const result: any = await this.ctx.model.Activity.update(
            { activity_status: 3 },
            { where: { activity_id: param.activity_id } }
        );
        if (result[0] <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '操作失败' }));
        }
    }

    /**
     * 创建活动
     * @param param 
     */
    public async createActivity(param: any) {
        const type = param.activity_type;
        if (type == 2) {
            const resultSMS: any = await this.ctx.model.IdentifyingCode.findOne<IdentifyingCode>({
                attributes: ['identifying_code_id', 'created_at'],
                where: { identifying_code: param.activity_code, phone: param.activity_sponsors_phone, identifying_type: '2' }
            });
            if (!resultSMS) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码不正确' }));
            }
            const { created_at } = resultSMS;
            const date = new Date();
            const currentDate = date.toLocaleString();
            const date1 = new Date(created_at);
            const date2 = new Date(currentDate);
            const date3 = date2.getTime() - date1.getTime();
            const leave1 = date3 % (24 * 3600 * 1000);
            const leave2 = leave1 % (3600 * 1000);        //计算小时数后剩余的毫秒数
            const minutes = Math.floor(leave2 / (60 * 1000));
            if (minutes > 5) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码已过期' }));
            }
        }
        param.activity_id = this.ctx.helper.genUUID();
        param.activity_auditing_status = '2';
        param.activity_status = '1';
        param.is_delete = '1';
        const tran = await this.ctx.model.transaction();
        try {
            const activity = await this.ctx.model.Activity.create(param, { transaction: tran });
            if (!activity) {
                throw new Error(JSON.stringify({ code: 2, msg: '添加失败' }));
            }
            const { activity_school_id } = param;
            if (activity_school_id && activity_school_id.length > 0) {
                this.ctx.model.ReactActivity.update({ react_activity_num: this.app.Sequelize.literal('`react_activity_num`+1') }, { where: { school_id: activity_school_id } });
            } else {
                this.ctx.model.ReactActivity.update({ react_activity_num: this.app.Sequelize.literal('`react_activity_num`+1') }, { where: { is_delete: '1' } });
            }
            await tran.commit();
        } catch (e) {
            await tran.rollback();
            this.ctx.logger.error(e);
            throw new Error(JSON.stringify({ code: 2, msg: '创建失败' }));
        }
    }

    /**
     * 创建活动-微信
     * @param param 
     */
    public async createWxActivity(param: any) {
        const type = param.activity_type;
        if (type == 2) {
            const resultSMS: any = await this.ctx.model.IdentifyingCode.findOne<IdentifyingCode>({
                attributes: ['identifying_code_id', 'created_at'],
                where: { identifying_code: param.activity_code, phone: param.activity_sponsors_phone, identifying_type: '2' }
            });
            if (!resultSMS) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码不正确' }));
            }
            const { created_at } = resultSMS;
            const date = new Date();
            const currentDate = date.toLocaleString();
            const date1 = new Date(created_at);
            const date2 = new Date(currentDate);
            const date3 = date2.getTime() - date1.getTime();
            const leave1 = date3 % (24 * 3600 * 1000);
            const leave2 = leave1 % (3600 * 1000);        //计算小时数后剩余的毫秒数
            const minutes = Math.floor(leave2 / (60 * 1000));
            if (minutes > 5) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码已过期' }));
            }
        }
        param.activity_id = this.ctx.helper.genUUID();
        if (param.activity_suto && param.activity_suto.length > 0) {
            param.activity_auditing_status = '1';
        } else {
            param.activity_auditing_status = '2';
        }
        param.activity_status = '1';
        param.is_delete = '1';
        const tran = await this.ctx.model.transaction();
        try {
            const activity = await this.ctx.model.Activity.create(param, { transaction: tran });
            if (!activity) {
                throw new Error(JSON.stringify({ code: 2, msg: '添加失败' }));
            }
            if (param.activity_auditing_status === '2') {
                const { activity_school_id } = param;
                if (activity_school_id && activity_school_id.length > 0) {
                    this.ctx.model.ReactActivity.update({ react_activity_num: this.app.Sequelize.literal('`react_activity_num`+1') }, { where: { school_id: activity_school_id } });
                } else {
                    this.ctx.model.ReactActivity.update({ react_activity_num: this.app.Sequelize.literal('`react_activity_num`+1') }, { where: { is_delete: '1' } });
                }
            }
            await tran.commit();
        } catch (e) {
            await tran.rollback();
            this.ctx.logger.error(e);
            throw new Error(JSON.stringify({ code: 2, msg: '创建失败' }));
        }
    }

    /**
     * 根据学校名模糊查找学校
     * @param param 
     */
    public async searchSchoolByName(param: any) {
        let submitParam: any = {};
        const Op = this.ctx.model.Op;
        if (param.school_name) {
            submitParam.school_name = { [Op.like]: '%' + param.school_name + '%' };
        }
        submitParam.school_status = '1';
        const school = await this.ctx.model.School.findAll<School>({
            attributes: ['school_id', 'school_name'],
            where: submitParam
        })
        if (school.length <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
        }
        return school;
    }

    /**
     * 获取每日活动发起数
     * @param param 
     */
    public async getActivityNum(param: any) {
        const resultDate: any = await this.ctx.helper.getFullData(param.startTime, param.endTime);
        const startTime = param.startTime + ' 00:00:00';
        const endTime = param.endTime + ' 23:59:59';
        const queryStr = `
            SELECT
                DATE_FORMAT( created_at, '%Y-%m-%d' ) AS date,
                COUNT( * ) total 
            FROM
                t_activity 
            WHERE
                created_at BETWEEN :startTime 
                AND :endTime
            GROUP BY
                DATE_FORMAT( created_at, '%Y-%m-%d' )`;
        const resultActivity: any = await this.ctx.model.query(
            queryStr,
            {
                type: this.ctx.app.Sequelize.QueryTypes.SELECT,
                replacements: { startTime: startTime, endTime: endTime }
            }
        )
        if (resultActivity.length <= 0) {
            return resultDate;
        }
        for (let i = 0; i < resultActivity.length; i++) {
            for (let j = 0; j < resultDate.length; j++) {
                if (resultActivity[i].date === resultDate[j].date) {
                    resultDate[j].total = resultActivity[i].total;
                }
            }
        }
        return resultDate;
    }

    /**
     * 活动报名
     * @param param 
     */
    public async activitySignIn(param: any) {
        const type = param.type;
        if (type == 1) {
            const { phone, code, weChat_account } = param;
            const resultSMS: any = await this.ctx.model.IdentifyingCode.findOne<IdentifyingCode>({
                attributes: ['identifying_code_id', 'created_at'],
                where: { identifying_code: code, phone: phone, identifying_type: '3' }
            });
            if (!resultSMS) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码不正确' }));
            }
            const { created_at } = resultSMS;
            const date = new Date();
            const currentDate = date.toLocaleString();
            const minutes1 = this.ctx.helper.getMinutes(created_at, currentDate);
            if (minutes1 > 5) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码已过期' }));
            }
            const activity_id = param.activity_id;
            const user_id = param.user_id;
            const resultActivity: any = await this.ctx.model.Activity.findOne<Activity>({
                attributes: ['activity_end_time'],
                where: { activity_id, is_delete: '1' }
            });
            const activity_end_time = resultActivity.activity_end_time;
            const dateNow = date.toLocaleString();
            const minutes2 = this.ctx.helper.getMinutes(activity_end_time, dateNow);
            if (minutes2 >= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '已超过活动时间，不能报名' }));
            }
            const result: any = await this.ctx.model.ActivityEnroll.findOne<ActivityEnroll>({
                attributes: ['activity_enroll_id', 'activity_enroll_status'],
                where: { activity_id, user_id, is_delete: '1' }
            })
            if (!result) {
                param.activity_enroll_id = this.ctx.helper.genUUID();
                param.is_delete = '1';
                param.activity_enroll_status = '1';
                const createResult = await this.ctx.model.ActivityEnroll.create(param);
                if (!createResult) {
                    throw new Error(JSON.stringify({ code: 2, msg: '报名失败' }));
                }
            } else {
                const activity_enroll_status = result.activity_enroll_status;
                if (activity_enroll_status == 1) {
                    throw new Error(JSON.stringify({ code: 2, msg: '您已报名，不能重复报名' }));
                } else if (activity_enroll_status == 2) {
                    throw new Error(JSON.stringify({ code: 2, msg: '您已被谢绝参加此活动，不能报名' }));
                } else if (activity_enroll_status == 3) {
                    const updateResult = await this.ctx.model.ActivityEnroll.update({ activity_enroll_status: '1', phone, weChat_account }, { where: { activity_enroll_id: result.activity_enroll_id } });
                    if (!updateResult) {
                        throw new Error(JSON.stringify({ code: 2, msg: '取消失败' }));
                    }
                }
            }
        } else if (type == 2) {
            const activity_id = param.activity_id;
            const user_id = param.user_id;
            const result: any = await this.ctx.model.ActivityEnroll.findOne<ActivityEnroll>({
                attributes: ['activity_enroll_id', 'activity_enroll_status'],
                where: { activity_id, user_id, is_delete: '1' }
            })
            if (!result) {
                throw new Error(JSON.stringify({ code: 2, msg: '您还未参加该活动，不能取消' }));
            } else {
                const activity_enroll_status = result.activity_enroll_status;
                if (activity_enroll_status == 1) {
                    const cancelResult = await this.ctx.model.ActivityEnroll.update({ activity_enroll_status: '3' }, { where: { activity_enroll_id: result.activity_enroll_id } });
                    if (!cancelResult) {
                        throw new Error(JSON.stringify({ code: 2, msg: '取消失败' }));
                    }
                } else if (activity_enroll_status == 2) {
                    throw new Error(JSON.stringify({ code: 2, msg: '您已被谢绝参加此活动，不能取消' }));
                } else if (activity_enroll_status == 3) {
                    throw new Error(JSON.stringify({ code: 2, msg: '您已取消此次活动，不能重复取消' }));
                }
            }
        } else {
            throw new Error(JSON.stringify({ code: 2, msg: '无法识别的操作类型' }));
        }
    }

    /**
     * 谢绝参加
     * @param param 
     */
    public async activityReject(param: any) {
        const result = await this.ctx.model.ActivityEnroll.update({ activity_enroll_status: '2' }, { where: { activity_enroll_id: param.activity_enroll_id } });
        if (result[0] <= 0) {
            throw new Error(JSON.stringify({ code: 1, msg: '操作失败' }));
        }
    }

    /**
     * 根据用户id查找用户创建的活动以及参与的活动列表
     * @param param 
     */
    public async searchActivityListById(param: any) {
        const queryMyActivity = `
        SELECT
            b.activity_id,
            b.activity_title,
            b.user_id,
            b.activity_start_time,
            b.activity_end_time,
            b.activity_place,
            b.activity_suto,
            b.activity_detail,
            b.activity_detail_picture,
            b.activity_cover,
            b.activity_status,
            b.activity_auditing_status,
            b.created_at,
            s.school_name,
            c.college_name
        FROM
            t_activity b
            INNER JOIN t_school s ON b.activity_school_id=s.school_id AND s.school_status='1'
            INNER JOIN t_user u ON b.user_id=u.user_id AND u.user_status<>'1'
            LEFT JOIN t_college c ON b.activity_college_id=c.college_id AND c.college_status='1'
        WHERE
            b.user_id=:user_id ORDER BY b.created_at DESC`;
        const resultActivity: any = await this.ctx.model.query(queryMyActivity, {
            type: this.ctx.model.QueryTypes.SELECT,
            replacements: { user_id: param.user_id }
        });
        const queryStr = `
            SELECT
                b.activity_id
            FROM
                t_activity_enroll a
                INNER JOIN (
                    (
                SELECT
                    a.activity_id,
                    a.user_id,
                    a.created_at
                FROM
                    t_activity a
                    INNER JOIN t_user u ON a.user_id = u.user_id 
                    AND u.user_status <> '1' 
                    AND activity_type = '2'
                    INNER JOIN t_college c1 ON u.user_college_id = c1.college_id 
                    AND c1.college_status = '1'
                    INNER JOIN t_school s ON a.activity_school_id = s.school_id 
                    AND s.school_status = '1'
                    LEFT JOIN t_college c ON a.activity_college_id = c.college_id 
                    AND c.college_status = '1' 
                    ) UNION
                    (
                SELECT
                    a.activity_id,
                    a.user_id,
                    a.created_at 
                FROM
                    t_activity a
                    INNER JOIN t_school s ON a.activity_school_id = s.school_id 
                    AND s.school_status = '1' 
                WHERE
                    a.is_delete = '1' 
                    AND a.activity_type = '1' 
                    ) UNION (SELECT
                    activity_id,
                    user_id,
                    created_at
                FROM
                    t_activity 
                WHERE
                    is_delete = '1' 
                    AND activity_type = '1' 
                    AND activity_school_id IS NULL
                    OR activity_school_id ='' )
                    ) as b ON a.activity_id = b.activity_id 
                AND a.activity_enroll_status='1' 
                AND a.user_id = :user_id 
                ORDER BY b.created_at  
                `;
        const resultActivityEnroll: any = await this.ctx.model.query(queryStr, {
            type: this.ctx.model.QueryTypes.SELECT,
            replacements: { user_id: param.user_id }
        });
        if (resultActivity.length <= 0 && resultActivityEnroll.length <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
        }
        let returnData: any = [];
        for (let i = 0; i < resultActivity.length; i++) {
            let isRepeat: boolean = false;
            for (let j = 0; j < resultActivityEnroll.length; j++) {
                if (resultActivity[i].activity_id === resultActivityEnroll[j].activity_id) {
                    isRepeat = true;
                    break;
                }
            }
            if (isRepeat) {
                continue;
            }
            let item: any = {};
            item.activity_id = resultActivity[i].activity_id;
            item.activity_title = resultActivity[i].activity_title;
            item.user_id = resultActivity[i].user_id;
            item.activity_start_time = resultActivity[i].activity_start_time;
            item.activity_end_time = resultActivity[i].activity_end_time;
            item.activity_place = resultActivity[i].activity_place;
            item.activity_suto = resultActivity[i].activity_suto;
            item.activity_detail = resultActivity[i].activity_detail;
            item.activity_detail_picture = resultActivity[i].activity_detail_picture;
            item.activity_cover = resultActivity[i].activity_cover;
            item.activity_status = resultActivity[i].activity_status;
            item.activity_auditing_status = resultActivity[i].activity_auditing_status;
            item.school_name = resultActivity[i].school_name;
            item.college_name = resultActivity[i].college_name;
            item.is_self = '1';
            const submitData = {
                activity_id: item.activity_id,
                user_id: param.user_id
            }
            const is_enroll = await this.checkIsEnroll(submitData);
            item.is_enroll = is_enroll;
            returnData.push(item);

        }
        for (let i = 0; i < resultActivityEnroll.length; i++) {
            const queryMyActivity = `
        SELECT
            b.activity_id,
            b.activity_title,
            b.user_id,
            b.activity_start_time,
            b.activity_end_time,
            b.activity_place,
            b.activity_suto,
            b.activity_detail,
            b.activity_detail_picture,
            b.activity_cover,
            b.activity_status,
            b.activity_auditing_status,
            s.school_name,
            c.college_name
        FROM
            t_activity b
            LEFT JOIN t_school s ON b.activity_school_id=s.school_id
            LEFT JOIN t_college c ON b.activity_college_id=c.college_id
        WHERE
            b.activity_id=:activity_id`;
            const resultActivity: any = await this.ctx.model.query(queryMyActivity, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { activity_id: resultActivityEnroll[i].activity_id }
            });
            let item: any = {};
            item.activity_id = resultActivity[0].activity_id;
            item.activity_title = resultActivity[0].activity_title;
            item.user_id = resultActivity[0].user_id;
            item.activity_start_time = resultActivity[0].activity_start_time;
            item.activity_end_time = resultActivity[0].activity_end_time;
            item.activity_place = resultActivity[0].activity_place;
            item.activity_suto = resultActivity[0].activity_suto;
            item.activity_detail = resultActivity[0].activity_detail;
            item.activity_detail_picture = resultActivity[0].activity_detail_picture;
            item.activity_cover = resultActivity[0].activity_cover;
            item.activity_status = resultActivity[0].activity_status;
            item.activity_auditing_status = resultActivity[0].activity_auditing_status;
            item.school_name = resultActivity[0].school_name;
            item.college_name = resultActivity[0].college_name;
            item.is_self = '2';
            const submitData = {
                activity_id: item.activity_id,
                user_id: param.user_id
            }
            const is_enroll = await this.checkIsEnroll(submitData);
            item.is_enroll = is_enroll;
            returnData.push(item);
        }
        return returnData;
    }

    /**
     * 检查活动状态
     */
    public async checkActivityStatus() {
        const Op = this.ctx.app.Sequelize.Op;
        const resultActivity: any = await this.ctx.model.Activity.findAll<Activity>({
            attributes: ['activity_id', 'activity_end_time'],
            where: { activity_status: { [Op.between]: ['1', '2'] }, is_delete: '1' }
        });
        for (let i = 0; i < resultActivity.length; i++) {
            const date = new Date();
            const dateNow = date.toLocaleString();
            const activity_end_time = resultActivity[i].activity_end_time;
            const stime = new Date(activity_end_time).getTime();
            const etime = new Date(dateNow).getTime();
            const usedTime = etime - stime;  //两个时间戳相差的毫秒数	
            let minutes = usedTime / 60000;
            minutes = Number(minutes.toString().split('.')[0]);
            if (minutes >= 0) {
                await this.ctx.model.Activity.update({ activity_status: '3' }, { where: { activity_id: resultActivity[i].activity_id } });
            }
        }
    }

    /**
     * 审核活动
     * @param param 
     */
    public async auditActivity(param: any) {
        const { activity_id, type } = param;
        let status = '';
        if (type == 1) {
            status = '2';
        } else if (type == 2) {
            status = '3';
        } else {
            throw new Error(JSON.stringify({ code: 2, msg: '无法识别的操作类型' }));
        }
        const tran = await this.ctx.model.transaction();
        try {
            const result: any = await this.ctx.model.Activity.update({ activity_auditing_status: status }, { where: { activity_id }, transaction: tran });
            if (result[0] <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '审核失败' }));
            }
            if (status === '2') {
                const searchResult: any = await this.ctx.model.Activity.findOne<Activity>({ attributes: ['activity_school_id'], where: { activity_id },transaction:tran });
                const activity_school_id = searchResult.activity_school_id;
                if (activity_school_id && activity_school_id.length > 0) {
                    this.ctx.model.ReactActivity.update({ react_activity_num: this.app.Sequelize.literal('`react_activity_num`+1') }, { where: { school_id: activity_school_id } });
                } else {
                    this.ctx.model.ReactActivity.update({ react_activity_num: this.app.Sequelize.literal('`react_activity_num`+1') }, { where: { is_delete: '1' }});
                }
            }
            await tran.commit();
        } catch (e) {
            await tran.rollback();
            this.ctx.logger.error(e);
            throw new Error(JSON.stringify({ code: 2, msg: '审核失败' }));
        }
    }
}
export default ActivityService;